﻿Imports System
Imports System.IO
Imports System.Net
Imports System.Windows
Imports System.Windows.Controls
Imports System.Windows.Data
Imports System.Windows.Media
Imports System.Windows.Media.Animation
Imports System.Windows.Navigation
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports System.ComponentModel
Imports System.Collections.Generic
Imports System.Linq
Imports Microsoft.VisualBasic

Partial Public Class Database
  Public _DB As Char
  Private bLoaded As Boolean = False
  Private strLastDatabase As String = ""
  Private strLastTable As String = ""
  Private strLastColumn As String = ""
  Private bUniqueColumn As Boolean = False
	Private dtDatabases As DataTable
	Private dtTables As DataTable
	Private dtColumns As datatable

  Public Property DB() As Char
    Get
      Return _DB
    End Get
    Set(ByVal value As Char)
      _DB = value
    End Set
  End Property

	Public Shared ReadOnly IsReadyProperty As DependencyProperty = DependencyProperty.Register("IsReady", _
																																														 GetType(TriState), _
																																														 GetType(Database))
	Public Property IsReady() As TriState
		Get
			Return DirectCast(Me.GetValue(IsReadyProperty), TriState)
		End Get
		Set(ByVal value As TriState)
			Me.SetValue(IsReadyProperty, value)
			If value = TriState.UseDefault Then
				lblNotUnique.Visibility = Windows.Visibility.Visible
			Else
				lblNotUnique.Visibility = Windows.Visibility.Hidden
			End If
		End Set
	End Property

  Private Function GetIsReady() As TriState
    If cmbDatabase IsNot Nothing AndAlso lbTable IsNot Nothing AndAlso cmbDatabase.Text > "" AndAlso lbTable.SelectedIndex >= 0 AndAlso lbColumn.SelectedIndex >= 0 AndAlso _DB > "" AndAlso Base.sqlConn(_DB) IsNot Nothing AndAlso Base.sqlConn(_DB).State = ConnectionState.Open Then
      If bUniqueColumn Then
        Return TriState.True
      Else
        Return TriState.UseDefault
      End If
    Else
      Return TriState.False
    End If
  End Function

  Public Sub New()
    MyBase.New()

    Me.InitializeComponent()

    Dim dpdComboBox As DependencyPropertyDescriptor = DependencyPropertyDescriptor.FromProperty(ComboBox.TextProperty, GetType(ComboBox))
    dpdComboBox.AddValueChanged(cmbDatabase, AddressOf OnDatabaseChanged)
  End Sub

	Private function SetDatabase(strDatabase As string) As boolean
    If strDatabase > "" andalso dtDatabases isnot nothing Then
      Dim rows() As DataRow = dtDatabases.Select("database_name = '" & strDatabase & "'")
      If rows IsNot Nothing AndAlso rows.Length > 0 Then
        cmbDatabase.Text = strDatabase
        Return true
      End If
    End If
	  Return false
	End function
	
	Private function SetTable(strTable As string) As boolean
    If strTable > "" Then
      Dim rows() As DataRow = dtTables.Select("TABLE_NAME = '" & strTable & "'")
      If rows IsNot Nothing AndAlso rows.Length > 0 Then
        lbTable.SelectedValue = strTable
        Return true
      End If
    End If
    Return false
	End function
	
	Private function SetColumn(strColumn As string) As boolean
		If strColumn > "" Then
      If dtColumns.Columns.Contains(strColumn) Then
        lbColumn.SelectedValue = strColumn
        Return true
      End If
    End If
    Return false
	End function

	Public Sub LoadDatabase()
		Base.ReOpenConnections()

		If Not cmbDatabase Is Nothing AndAlso Not Base.sqlConn(_DB) Is Nothing AndAlso Base.sqlConn(_DB).State = System.Data.ConnectionState.Open Then
			Try
				Dim strDatabase As String = strLastDatabase
				cmbDatabase.Text = ""
				
				dtDatabases = Base.sqlConn(_DB).GetSchema(SqlClientMetaDataCollectionNames.Databases)
				dtDatabases.Rows.InsertAt(dtDatabases.NewRow, 0)
				dtDatabases.Rows(0)("database_name") = ""
				Dim dv As New DataView(dtDatabases, "not database_name = 'tempdb'", "database_name", DataViewRowState.CurrentRows)
				cmbDatabase.ItemsSource = dv
				cmbDatabase.DisplayMemberPath = "database_name"
				SetDatabase(strDatabase)

				bLoaded = True
			Catch ex As Exception
				Base.WriteToConsole(ex.ToString, True)
			End Try
		End If
	End Sub

  Private Sub OnDatabaseChanged()
    If bLoaded Then
      If cmbDatabase IsNot Nothing AndAlso cmbDatabase.Text > "" AndAlso Base.sqlConn(_DB) IsNot Nothing Then
        Try
          If Not Base.sqlConn(_DB).State = ConnectionState.Open Then
            Base.sqlConn(_DB).Open()
          End If
					
					Dim strTable As String = strLastTable

          lbTable.SelectedIndex = -1
          lbTable.ItemsSource = Nothing
          Base.sqlConn(_DB).ChangeDatabase(cmbDatabase.Text)

          dtTables = Base.sqlConn(_DB).GetSchema(SqlClientMetaDataCollectionNames.Tables)
          Dim tables As IEnumerable(Of Object) = (From row In dtTables.AsEnumerable Order By row("TABLE_NAME") Select row("TABLE_NAME")).AsEnumerable
          lbTable.ItemsSource = tables
					SetTable(strTable)

          strLastDatabase = cmbDatabase.Text

          OnTableChanged()
        Catch ex As Exception
					Base.WriteToConsole(ex.ToString, False)
          lbTable.ItemsSource = Nothing
          lbColumn.ItemsSource = Nothing
          Base.sqlConn(_DB).Close()
        End Try
      Else
        lbTable.ItemsSource = Nothing
      End If
    End If
  End Sub

  Private Sub OnTableChanged() Handles lbTable.SelectionChanged
    If bLoaded Then
      If lbTable IsNot Nothing AndAlso lbTable.SelectedValue IsNot Nothing AndAlso Base.sqlConn(_DB) IsNot Nothing Then
        Try
					Dim strColumn As String = strLastColumn
          lbColumn.SelectedIndex = -1
          lbColumn.ItemsSource = nothing
          'bUniqueColumn = False
				
					Dim strCurrentTable As String = lbTable.SelectedValue.ToString
					Dim iPrimaryKeyIndex As Integer = 0
					Dim strPrimaryKey As String = ""
					Dim cmd As New SqlCommand("SELECT TOP 1 * FROM [" & Base.TableForQuery(strCurrentTable) & "];", Base.sqlConn(_DB))
					Dim da As New SqlDataAdapter(cmd)
					dtColumns = New DataTable
					da.FillSchema(dtColumns, SchemaType.Source)

					If dtColumns.PrimaryKey.Length > 0 Then
						For i As Integer = 0 To dtColumns.PrimaryKey.Length - 1
							If dtColumns.PrimaryKey(i).Unique Then
								' Set the primary key index as the first unique column found
								iPrimaryKeyIndex = i
								Exit For
							End If
						Next

						strPrimaryKey = dtColumns.PrimaryKey(iPrimaryKeyIndex).ColumnName
						'bUniqueColumn = dtTable.PrimaryKey(iPrimaryKeyIndex).Unique
						'Base.WriteToConsole(Me, strCurrentTable & "." & strPrimaryKey & " = " & dt.PrimaryKey(iPrimaryKeyIndex).Unique)
					End If

					lbColumn.ItemsSource = dtColumns.Columns
					lbColumn.DisplayMemberPath = "ColumnName"
					lbColumn.SelectedValuePath = "ColumnName"
					'lbColumn.UpdateLayout()

					If Not SetColumn(strColumn) Then
						If strPrimaryKey > "" Then
							lbColumn.SelectedValue = strPrimaryKey
						Else
							lbColumn.SelectedIndex = -1
						End If
					End If

					strLastTable = lbTable.SelectedValue.ToString

					OnColumnChanged()
				Catch ex As Exception
					Base.WriteToConsole(ex.ToString, False)
					lbColumn.ItemsSource = Nothing
				End Try
			Else
				lbColumn.ItemsSource = Nothing
			End If
		End If
  End Sub

  Private Sub OnColumnChanged() Handles lbColumn.SelectionChanged
    If bLoaded Then
      If lbTable IsNot Nothing AndAlso lbTable.SelectedValue IsNot Nothing AndAlso lbColumn IsNot Nothing AndAlso lbColumn.SelectedValue IsNot Nothing AndAlso Base.sqlConn(_DB) IsNot Nothing Then
        Try
          bUniqueColumn = dtColumns.Columns(lbColumn.SelectedValue.ToString).Unique
        Catch ex As Exception
					Base.WriteToConsole(ex.ToString, False)
        End Try
        strLastColumn = lbColumn.SelectedValue.ToString
      End If
      IsReady = GetIsReady()
    End If
  End Sub
End Class
